
import pymysql
import pandas as pd
import asyncio
import time
import datetime
import traceback
import os
import sys
# print(os.path.join(os.path.split(os.path.split(sys.path[0])[0])[0], "bin"))
# # 添加导包路径
# sys.path.append(os.path.join(os.path.split(os.path.split(sys.path[0])[0])[0], "bin"))
sys.path.append(os.path.split(os.path.split(sys.path[0])[0])[0])

from bin.openapi import OpenApiBase
from sqlalchemy import create_engine

class ad_father:

    def __init__(self):
        self.op_api = OpenApiBase("https://openapi.lingxing.com", "ak_A77HkbumPXlk3", "6z+ZCTX2z5ctgITSuH+akQ==") 
        self.access_token = ""
        self.refresh_token = ""

    async def generate_access_token(self):
        resp = await self.op_api.generate_access_token()
        self.access_token = resp.access_token
        self.refresh_token = resp.refresh_token
        assert resp.access_token
        # return resp.dict()
    
    async def refresh_token_handler(self):
        resp = await self.op_api.refresh_token(self.refresh_token)
        self.refresh_token =  resp.refresh_token
        self.access_token = resp.access_token
        assert resp.access_token

    async def check_token(self, data):
        if data['code'] == 0:
            return True
        elif data['code'] in [2001005, 2001003, 2001008, 2001009]:
            await self.generate_access_token()
            return False
        else:
            print(data['code'])
            return False

class db_tools:
    def __init__(self):
        self.engine = create_engine('mysql+pymysql://user_rpa:123456@192.168.8.10:3306/ad_api?charset=utf8mb4')   # 西安数据库
        self.conn = pymysql.connect(user='user_rpa', password='123456', host='192.168.8.10', database='ad_api') # 西安数据库
        # self.engine = create_engine('mysql+pymysql://root:123456@192.168.8.200:3306/new_ad?charset=utf8mb4')  # 河津数据库
        # self.conn = pymysql.connect(user='root', password='123456', host='192.168.8.200', database='new_ad') # 河津数据库
        self.cur = self.conn.cursor()
    
    def insert_data(self, data, USER_TABLE_NAME):
        data.to_sql(USER_TABLE_NAME, con=self.engine, index=False, if_exists="append")
    

class tools:


    camp = 0
    searc = 0

    @staticmethod
    def get_sid_accountname(data):
        sid = data['sid']
        account_name = data['account_name'].split("-")[0].replace("账号", "")
        return sid, account_name
    @staticmethod
    def get_date_lst(start_date, end_date):
        start_date = datetime.datetime.strptime(start_date, "%Y-%m-%d")
        end_date = datetime.datetime.strptime(end_date, "%Y-%m-%d")
        date_lst = []
        while start_date != end_date:
            date_lst.append(start_date.strftime("%Y-%m-%d"))
            start_date = start_date+datetime.timedelta(days=-1)
        return date_lst

class ad_sp_hour(ad_father):

    def __init__(self):
        super().__init__()
        self.db = db_tools()
        self.today = datetime.datetime.now().strftime("%Y-%m-%d")
    
    def get_account(self, country):
        sql = f"select sid, name, account_name, country from account_info where country = '{country}' and status = 1 and has_ads_setting = 1"
        data_account = pd.read_sql(sql, con=self.db.engine)
        data_account = data_account.drop_duplicates()
        print(data_account)
        return data_account


    async def sp_data_handler(self, url, report_date, account, campaign_id, sid):
        req_body = {
            "report_date":report_date,
            "campaign_id":campaign_id
        }
        
        
        if url in ["/pb/openapi/newad/spAdvertiseHourData", "/pb/openapi/newad/spTargetHourData"]:
            req_body['agg_dimension'] = "both_ad_target"
        print(f"{report_date} 日期 {url}数据开始抓取")
        res_lst = []
        while 1:
            try:


                res = await self.op_api.request(self.access_token, url, "POST", req_body=req_body)
            except:
                print("异常：", traceback.format_exc())
                # print(self.access_token, url, method, req_body)
                # time.sleep(10)
                continue
            resp = res.dict()
            # print(resp)
            if not await self.check_token(resp):
                continue
            if len(resp['data']) != 0:
                res_lst = res_lst + resp['data']
            if len(res_lst) == 0:
                break
        if len(res_lst) == 0:
            return []
        res_df = pd.DataFrame(res_lst)
        res_df['sid'] = sid
        res_df['account'] = account
        res_df['update_time'] = pd.to_datetime(datetime.datetime.now())
        return res_df


    def get_campaign_id_data_sp(self):
        query = f"select campaign_id, profile_id, name, sid, account, portfolio_id  from sp_campaigns_data_api where  state = 'enabled' and DATE(update_time) = (select DATE(max(update_time)) from sp_campaigns_data_api)"
        query_data = pd.read_sql(query, con=self.db.engine)
        return query_data

    def get_date_lst(self):
        date_today = datetime.datetime.now()+ datetime.timedelta(days=-2)
        end_date = date_today + datetime.timedelta(days=-3)
        print(f"日期范围：{date_today}到{end_date}")

        date_today_str = date_today.strftime("%Y-%m-%d")
        end_date_str = end_date.strftime("%Y-%m-%d")

        date_lst = tools.get_date_lst(date_today_str, end_date_str)
        return date_lst
    
    def delete_data(self, table_name_lst, date_str):
        for table_name in table_name_lst:
            sql = f"""DELETE FROM {table_name} WHERE (report_date in ({date_str}) ) ;"""
            self.db.cur.execute(sql)
        self.db.conn.commit()
        print("各个表数据已删除")
    
    def get_account_by_campaign_id(self, campaign_id, query_data):
        query_data_one = query_data[query_data["campaign_id"] == campaign_id]
        accout_lst = list(query_data_one['account'])
        sid_lst = list(query_data_one['sid'])
        if len(accout_lst)> 0:

            return accout_lst[0], sid_lst[0]
        else:
            return "Null"
    
    async def sp_campaign_hour_api(self, campaign_id_one, report_date, account, sid):
        url = "/pb/openapi/newad/spCampaignHourData"
        res_df = await self.sp_data_handler(url, report_date, account, campaign_id_one, sid)
        if len(res_df)>0:
            self.db.insert_data(res_df, "sp_campaign_hour_api")
            print(f"{report_date} 日期 sp_campaign_hour_api 数据已插入")
        else:
            print("无数据")

    async def sp_splacement_hour_api(self, campaign_id_one, report_date, account, sid):
        url = "/pb/openapi/newad/spAdPlacementHourData"
        res_df = await self.sp_data_handler(url, report_date, account, campaign_id_one, sid)
        if len(res_df)>0:
            self.db.insert_data(res_df, "sp_splacement_hour_api")
            print(f"{report_date} 日期 sp_splacement_hour_api 数据已插入")
        else:
            print("无数据")

    async def sp_ad_group_hour_api(self, campaign_id_one, report_date, account, sid):
        url = "/pb/openapi/newad/spAdGroupHourData"
        res_df = await self.sp_data_handler(url, report_date, account, campaign_id_one, sid)
        if len(res_df)>0:
            self.db.insert_data(res_df, "sp_ad_group_hour_api")
            print(f"{report_date} 日期 sp_ad_group_hour_api 数据已插入")
        else:
            print("无数据")
    
    async def sp_advertise_hour_api(self, campaign_id_one, report_date, account, sid):
        url = "/pb/openapi/newad/spAdvertiseHourData"
        res_df = await self.sp_data_handler(url, report_date, account, campaign_id_one, sid)
        if len(res_df)>0:
            self.db.insert_data(res_df, "sp_advertise_hour_api")
            print(f"{report_date} 日期 sp_advertise_hour_api 数据已插入")
        else:
            print("无数据")
    
    async def sp_target_hour_api(self, campaign_id_one, report_date, account, sid):
        url = "/pb/openapi/newad/spTargetHourData"
        res_df = await self.sp_data_handler(url, report_date, account, campaign_id_one, sid)
        if len(res_df)>0:
            self.db.insert_data(res_df, "sp_target_hour_api")
            print(f"{report_date} 日期 sp_target_hour_api 数据已插入")
        else:
            print("无数据")
    
    async def sp_handler(self, semaphore, hanlder, report_date, campaign_id_one, account, sid):
        async with semaphore:
            await hanlder(campaign_id_one, report_date, account, sid)


    async def main(self):

        date_lst = self.get_date_lst()
        # 所有表的表名
        table_name_lst = ['sp_campaign_hour_api', 'sp_splacement_hour_api', 'sp_ad_group_hour_api', 'sp_advertise_hour_api', 'sp_target_hour_api']
        date_str = ",".join(["'" + i + "'" for i in date_lst])

        self.delete_data(table_name_lst, date_str)

        query_data = self.get_campaign_id_data_sp()

        sp_campaign_hour_api_semaphore = asyncio.Semaphore(1)
        sp_splacement_hour_api_semaphore = asyncio.Semaphore(1)
        sp_ad_group_hour_api_semaphore = asyncio.Semaphore(1)
        sp_advertise_hour_api_semaphore = asyncio.Semaphore(1)
        sp_target_hour_api_semaphore = asyncio.Semaphore(1)

        await self.generate_access_token()



        tasks = []
        for report_date in date_lst:
            for table_name in table_name_lst:
                for campaign_id_one in set(query_data['campaign_id']):
                    account, sid = self.get_account_by_campaign_id(campaign_id_one, query_data)
                    tasks.append(self.sp_handler(sp_campaign_hour_api_semaphore, self.sp_campaign_hour_api, report_date, campaign_id_one, account, sid))
                    tasks.append(self.sp_handler(sp_splacement_hour_api_semaphore, self.sp_splacement_hour_api, report_date, campaign_id_one, account, sid))
                    tasks.append(self.sp_handler(sp_ad_group_hour_api_semaphore, self.sp_ad_group_hour_api, report_date, campaign_id_one, account, sid))
                    tasks.append(self.sp_handler(sp_advertise_hour_api_semaphore, self.sp_advertise_hour_api, report_date, campaign_id_one, account, sid))
                    tasks.append(self.sp_handler(sp_target_hour_api_semaphore, self.sp_target_hour_api, report_date, campaign_id_one, account, sid))

        result = await asyncio.gather(*tasks)


def run():
    start = time.time()
    ad_sp_h = ad_sp_hour()
    asyncio.run(ad_sp_h.main())

    end = time.time()
    print("共耗时:", end-start)


if __name__ == "__main__":
    run()


